<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>子查询 | 知识库</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/noteslibrary/img/favicon.ico">
    <meta name="description" content="Java后端技术博客,专注Java后端学习与总结。HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github等技术文章。">
    <meta name="keywords" content="后端博客,个人技术博客,后端,后端开发,后端框架,后端面试题,技术文档,学习,面试,HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/noteslibrary/assets/css/0.styles.2500ff6d.css" as="style"><link rel="preload" href="/noteslibrary/assets/js/app.dc0c3d24.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/2.a664539c.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/96.440e6bbe.js" as="script"><link rel="prefetch" href="/noteslibrary/assets/js/10.bab1a8b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/100.245d2d1d.js"><link rel="prefetch" href="/noteslibrary/assets/js/101.9d1c6c13.js"><link rel="prefetch" href="/noteslibrary/assets/js/102.d187686f.js"><link rel="prefetch" href="/noteslibrary/assets/js/103.b0e7acf6.js"><link rel="prefetch" href="/noteslibrary/assets/js/104.895c9e53.js"><link rel="prefetch" href="/noteslibrary/assets/js/105.235036be.js"><link rel="prefetch" href="/noteslibrary/assets/js/106.17a71ae1.js"><link rel="prefetch" href="/noteslibrary/assets/js/107.256ceb5d.js"><link rel="prefetch" href="/noteslibrary/assets/js/108.57832d85.js"><link rel="prefetch" href="/noteslibrary/assets/js/109.f8b1d1c7.js"><link rel="prefetch" href="/noteslibrary/assets/js/11.2c69801a.js"><link rel="prefetch" href="/noteslibrary/assets/js/110.f3ce6a68.js"><link rel="prefetch" href="/noteslibrary/assets/js/111.bdfd28f4.js"><link rel="prefetch" href="/noteslibrary/assets/js/112.878f599c.js"><link rel="prefetch" href="/noteslibrary/assets/js/113.0efa07cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/114.c7aba251.js"><link rel="prefetch" href="/noteslibrary/assets/js/115.8b2e628d.js"><link rel="prefetch" href="/noteslibrary/assets/js/116.3392d8ba.js"><link rel="prefetch" href="/noteslibrary/assets/js/117.310538d0.js"><link rel="prefetch" href="/noteslibrary/assets/js/118.81eb015b.js"><link rel="prefetch" href="/noteslibrary/assets/js/12.e77f5a18.js"><link rel="prefetch" href="/noteslibrary/assets/js/13.d24d0054.js"><link rel="prefetch" href="/noteslibrary/assets/js/14.f29d4d33.js"><link rel="prefetch" href="/noteslibrary/assets/js/15.8694e081.js"><link rel="prefetch" href="/noteslibrary/assets/js/16.03ffb143.js"><link rel="prefetch" href="/noteslibrary/assets/js/17.83edd7b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/18.58ca4447.js"><link rel="prefetch" href="/noteslibrary/assets/js/19.e136d5c2.js"><link rel="prefetch" href="/noteslibrary/assets/js/20.fd3961b6.js"><link rel="prefetch" href="/noteslibrary/assets/js/21.207406c6.js"><link rel="prefetch" href="/noteslibrary/assets/js/22.b8738ce2.js"><link rel="prefetch" href="/noteslibrary/assets/js/23.33e4529d.js"><link rel="prefetch" href="/noteslibrary/assets/js/24.ab5493c5.js"><link rel="prefetch" href="/noteslibrary/assets/js/25.2506ce48.js"><link rel="prefetch" href="/noteslibrary/assets/js/26.7e6a9c14.js"><link rel="prefetch" href="/noteslibrary/assets/js/27.e7b4e92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/28.7ad46ba6.js"><link rel="prefetch" href="/noteslibrary/assets/js/29.81666f41.js"><link rel="prefetch" href="/noteslibrary/assets/js/3.8f13cd17.js"><link rel="prefetch" href="/noteslibrary/assets/js/30.07ada09b.js"><link rel="prefetch" href="/noteslibrary/assets/js/31.f271c8cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/32.2350914c.js"><link rel="prefetch" href="/noteslibrary/assets/js/33.9d7bfb77.js"><link rel="prefetch" href="/noteslibrary/assets/js/34.6fcf6f6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/35.0c3a88fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/36.574ca92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/37.72e38074.js"><link rel="prefetch" href="/noteslibrary/assets/js/38.16d408fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/39.63abc4a8.js"><link rel="prefetch" href="/noteslibrary/assets/js/4.3e68fc94.js"><link rel="prefetch" href="/noteslibrary/assets/js/40.ca2bdd48.js"><link rel="prefetch" href="/noteslibrary/assets/js/41.f82c80ec.js"><link rel="prefetch" href="/noteslibrary/assets/js/42.d1b8f579.js"><link rel="prefetch" href="/noteslibrary/assets/js/43.d3fd6260.js"><link rel="prefetch" href="/noteslibrary/assets/js/44.491f9afa.js"><link rel="prefetch" href="/noteslibrary/assets/js/45.1ee0d084.js"><link rel="prefetch" href="/noteslibrary/assets/js/46.a3e0be1f.js"><link rel="prefetch" href="/noteslibrary/assets/js/47.647908d1.js"><link rel="prefetch" href="/noteslibrary/assets/js/48.9658b8b0.js"><link rel="prefetch" href="/noteslibrary/assets/js/49.8f4b8327.js"><link rel="prefetch" href="/noteslibrary/assets/js/5.a0a97ccd.js"><link rel="prefetch" href="/noteslibrary/assets/js/50.f46d1433.js"><link rel="prefetch" href="/noteslibrary/assets/js/51.f1b784a0.js"><link rel="prefetch" href="/noteslibrary/assets/js/52.cce45956.js"><link rel="prefetch" href="/noteslibrary/assets/js/53.d66dabe3.js"><link rel="prefetch" href="/noteslibrary/assets/js/54.384b864b.js"><link rel="prefetch" href="/noteslibrary/assets/js/55.47ed19f2.js"><link rel="prefetch" href="/noteslibrary/assets/js/56.193cd456.js"><link rel="prefetch" href="/noteslibrary/assets/js/57.e6ea1f8c.js"><link rel="prefetch" href="/noteslibrary/assets/js/58.97fd2330.js"><link rel="prefetch" href="/noteslibrary/assets/js/59.b0c3d9ea.js"><link rel="prefetch" href="/noteslibrary/assets/js/6.50cbd75f.js"><link rel="prefetch" href="/noteslibrary/assets/js/60.d01d0651.js"><link rel="prefetch" href="/noteslibrary/assets/js/61.385e9bae.js"><link rel="prefetch" href="/noteslibrary/assets/js/62.a93fa4c8.js"><link rel="prefetch" href="/noteslibrary/assets/js/63.f72a2142.js"><link rel="prefetch" href="/noteslibrary/assets/js/64.3bf0b024.js"><link rel="prefetch" href="/noteslibrary/assets/js/65.cb1cb3bb.js"><link rel="prefetch" href="/noteslibrary/assets/js/66.4c9ff8cd.js"><link rel="prefetch" href="/noteslibrary/assets/js/67.2fc17900.js"><link rel="prefetch" href="/noteslibrary/assets/js/68.fd3ee410.js"><link rel="prefetch" href="/noteslibrary/assets/js/69.682be05d.js"><link rel="prefetch" href="/noteslibrary/assets/js/7.80203dee.js"><link rel="prefetch" href="/noteslibrary/assets/js/70.29428a45.js"><link rel="prefetch" href="/noteslibrary/assets/js/71.aff6ef6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/72.fe7572e0.js"><link rel="prefetch" href="/noteslibrary/assets/js/73.e52bc1a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/74.5e72ee84.js"><link rel="prefetch" href="/noteslibrary/assets/js/75.5a5bba64.js"><link rel="prefetch" href="/noteslibrary/assets/js/76.70d6ff7c.js"><link rel="prefetch" href="/noteslibrary/assets/js/77.1db1c302.js"><link rel="prefetch" href="/noteslibrary/assets/js/78.137c92e8.js"><link rel="prefetch" href="/noteslibrary/assets/js/79.8455d34b.js"><link rel="prefetch" href="/noteslibrary/assets/js/8.9e13e493.js"><link rel="prefetch" href="/noteslibrary/assets/js/80.127f3a4e.js"><link rel="prefetch" href="/noteslibrary/assets/js/81.191d3614.js"><link rel="prefetch" href="/noteslibrary/assets/js/82.6ae31745.js"><link rel="prefetch" href="/noteslibrary/assets/js/83.640cd3d7.js"><link rel="prefetch" href="/noteslibrary/assets/js/84.22c036e2.js"><link rel="prefetch" href="/noteslibrary/assets/js/85.692cd496.js"><link rel="prefetch" href="/noteslibrary/assets/js/86.793e38a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/87.eb27f1d6.js"><link rel="prefetch" href="/noteslibrary/assets/js/88.6d48c75e.js"><link rel="prefetch" href="/noteslibrary/assets/js/89.a798bacb.js"><link rel="prefetch" href="/noteslibrary/assets/js/9.7e864ff7.js"><link rel="prefetch" href="/noteslibrary/assets/js/90.e814aadc.js"><link rel="prefetch" href="/noteslibrary/assets/js/91.5c132772.js"><link rel="prefetch" href="/noteslibrary/assets/js/92.d0d1984d.js"><link rel="prefetch" href="/noteslibrary/assets/js/93.bbc81ca6.js"><link rel="prefetch" href="/noteslibrary/assets/js/94.242fbc29.js"><link rel="prefetch" href="/noteslibrary/assets/js/95.f7232d68.js"><link rel="prefetch" href="/noteslibrary/assets/js/97.a8ad3aa8.js"><link rel="prefetch" href="/noteslibrary/assets/js/98.e9a01f98.js"><link rel="prefetch" href="/noteslibrary/assets/js/99.e024f354.js">
    <link rel="stylesheet" href="/noteslibrary/assets/css/0.styles.2500ff6d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/noteslibrary/" class="home-link router-link-active"><img src="/noteslibrary/img/EB-logo.png" alt="知识库" class="logo"> <span class="site-name can-hide">知识库</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ea5ac/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ea5ac/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ea5ac/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ea5ac/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ea5ac/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ea5ac/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/noteslibrary/pages/32e9fd/" class="sidebar-link">写在前面</a></li><li><a href="/noteslibrary/pages/cfda94/" class="sidebar-link">数据库概述</a></li><li><a href="/noteslibrary/pages/497bcb/" class="sidebar-link">MySQL环境搭建</a></li><li><a href="/noteslibrary/pages/9c9f3a/" class="sidebar-link">基本的SELECT语句</a></li><li><a href="/noteslibrary/pages/b59830/" class="sidebar-link">运算符</a></li><li><a href="/noteslibrary/pages/6fef5b/" class="sidebar-link">排序与分页</a></li><li><a href="/noteslibrary/pages/3a50a8/" class="sidebar-link">多表查询</a></li><li><a href="/noteslibrary/pages/8de5b4/" class="sidebar-link">单行函数</a></li><li><a href="/noteslibrary/pages/2dfe6d/" class="sidebar-link">聚合函数</a></li><li><a href="/noteslibrary/pages/3ea5ac/" aria-current="page" class="active sidebar-link">子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3ea5ac/#_1-需求分析与问题解决" class="sidebar-link">1. 需求分析与问题解决</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_1-1-实际问题" class="sidebar-link">1.1 实际问题</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_1-2-子查询的基本使用" class="sidebar-link">1.2 子查询的基本使用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_1-3-子查询的分类" class="sidebar-link">1.3 子查询的分类</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3ea5ac/#_2-单行子查询" class="sidebar-link">2. 单行子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_2-1-单行比较操作符" class="sidebar-link">2.1 单行比较操作符</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_2-2-代码示例" class="sidebar-link">2.2 代码示例</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_2-3-having-中的子查询" class="sidebar-link">2.3 HAVING 中的子查询</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_2-4-case中的子查询" class="sidebar-link">2.4 CASE中的子查询</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_2-5-子查询中的空值问题" class="sidebar-link">2.5 子查询中的空值问题</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_2-5-非法使用子查询" class="sidebar-link">2.5 非法使用子查询</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3ea5ac/#_3-多行子查询" class="sidebar-link">3. 多行子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_3-1-多行比较操作符" class="sidebar-link">3.1 多行比较操作符</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_3-2-代码示例" class="sidebar-link">3.2 代码示例</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_3-3-空值问题" class="sidebar-link">3.3 空值问题</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3ea5ac/#_4-相关子查询" class="sidebar-link">4. 相关子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_4-1-相关子查询执行流程" class="sidebar-link">4.1 相关子查询执行流程</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_4-2-代码示例" class="sidebar-link">4.2 代码示例</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_4-3-exists-与-not-exists关键字" class="sidebar-link">4.3 EXISTS 与 NOT EXISTS关键字</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_4-4-相关更新" class="sidebar-link">4.4 相关更新</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_4-4-相关删除" class="sidebar-link">4.4 相关删除</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3ea5ac/#_5-抛一个思考题" class="sidebar-link">5. 抛一个思考题</a></li></ul></li></ul></li><li><a href="/noteslibrary/pages/70a90b/" class="sidebar-link">创建和管理表</a></li><li><a href="/noteslibrary/pages/a20c5b/" class="sidebar-link">数据处理之增删改</a></li><li><a href="/noteslibrary/pages/9515c0/" class="sidebar-link">MySQL数据类型精讲</a></li><li><a href="/noteslibrary/pages/98e681/" class="sidebar-link">约束</a></li><li><a href="/noteslibrary/pages/4535e0/" class="sidebar-link">视图</a></li><li><a href="/noteslibrary/pages/e5e604/" class="sidebar-link">存储过程与函数</a></li><li><a href="/noteslibrary/pages/f68b46/" class="sidebar-link">变量与流程控制与游标</a></li><li><a href="/noteslibrary/pages/fe941e/" class="sidebar-link">触发器</a></li><li><a href="/noteslibrary/pages/efad7b/" class="sidebar-link">MySQL8其它新特性</a></li></ul></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/noteslibrary/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/noteslibrary/database/#数据库" data-v-06225672>数据库</a></li><li data-v-06225672><a href="/noteslibrary/database/#MySQL笔记" data-v-06225672>MySQL笔记</a></li></ul> <div class="info" data-v-06225672><!----> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-04-05</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">子查询<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="子查询"><a href="#子查询" class="header-anchor">#</a> 子查询</h1> <p>子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。</p> <h2 id="_1-需求分析与问题解决"><a href="#_1-需求分析与问题解决" class="header-anchor">#</a> 1. 需求分析与问题解决</h2> <h3 id="_1-1-实际问题"><a href="#_1-1-实际问题" class="header-anchor">#</a> 1.1 实际问题</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928974.png" alt="image-20220610112600745"></p> <p>现有解决方式：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式一：</span>
<span class="token keyword">SELECT</span> salary
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Abel'</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span>salary
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> salary <span class="token operator">&gt;</span> <span class="token number">11000</span><span class="token punctuation">;</span>

<span class="token comment">#方式二：自连接</span>
<span class="token keyword">SELECT</span> e2<span class="token punctuation">.</span>last_name<span class="token punctuation">,</span>e2<span class="token punctuation">.</span>salary
<span class="token keyword">FROM</span> employees e1<span class="token punctuation">,</span>employees e2
<span class="token keyword">WHERE</span> e1<span class="token punctuation">.</span>last_name <span class="token operator">=</span> <span class="token string">'Abel'</span>
<span class="token operator">AND</span> e1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span> <span class="token operator">&lt;</span> e2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式三：子查询</span>
<span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span>salary
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> salary <span class="token operator">&gt;</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> salary
    <span class="token keyword">FROM</span> employees
    <span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Abel'</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928975.png" alt="image-20220610112649400"></p> <h3 id="_1-2-子查询的基本使用"><a href="#_1-2-子查询的基本使用" class="header-anchor">#</a> 1.2 子查询的基本使用</h3> <ul><li>子查询的基本语法结构：</li></ul> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928977.png" alt="image-20220610112714304"></p> <ul><li>子查询（内查询）在主查询之前一次执行完成。</li> <li>子查询的结果被主查询（外查询）使用 。</li> <li><strong>注意事项</strong> <ul><li>子查询要包含在括号内</li> <li>将子查询放在比较条件的右侧</li> <li>单行操作符对应单行子查询，多行操作符对应多行子查询</li></ul></li></ul> <h3 id="_1-3-子查询的分类"><a href="#_1-3-子查询的分类" class="header-anchor">#</a> 1.3 子查询的分类</h3> <p><strong>分类方式1：</strong>
我们按内查询的结果返回一条还是多条记录，将子查询分为 <code>单行子查询</code> 、 <code>多行子查询</code> 。</p> <ul><li>单行子查询</li></ul> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928978.png" alt="image-20220610112817243"></p> <ul><li>多行子查询</li></ul> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928979.png" alt="image-20220610112829078"></p> <p><strong>分类方式2：</strong>
我们按内查询是否被执行多次，将子查询划分为 <code>相关(或关联)子查询</code> 和 <code>不相关(或非关联)子查询</code> 。</p> <p>子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。</p> <p>同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。</p> <h2 id="_2-单行子查询"><a href="#_2-单行子查询" class="header-anchor">#</a> 2. 单行子查询</h2> <h3 id="_2-1-单行比较操作符"><a href="#_2-1-单行比较操作符" class="header-anchor">#</a> 2.1 单行比较操作符</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928980.png" alt="image-20220610112927341"></p> <h3 id="_2-2-代码示例"><a href="#_2-2-代码示例" class="header-anchor">#</a> 2.2 代码示例</h3> <p><strong>题目：查询工资大于149号员工工资的员工的信息</strong></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928981.png" alt="image-20220610112946706"></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928982.png" alt="image-20220610112952383"></p> <p><strong>题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span> job_id<span class="token punctuation">,</span> salary
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span> job_id <span class="token operator">=</span> 
       <span class="token punctuation">(</span><span class="token keyword">SELECT</span> job_id
        <span class="token keyword">FROM</span>  employees
        <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> <span class="token number">141</span><span class="token punctuation">)</span>
<span class="token operator">AND</span>  salary <span class="token operator">&gt;</span>
       <span class="token punctuation">(</span><span class="token keyword">SELECT</span> salary
        <span class="token keyword">FROM</span>  employees
        <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> <span class="token number">143</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928983.png" alt="image-20220610113017463"></p> <p><strong>题目：返回公司工资最少的员工的last_name,job_id和salary</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span> job_id<span class="token punctuation">,</span> salary
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span> salary <span class="token operator">=</span>
       <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
        <span class="token keyword">FROM</span>  employees<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928984.png" alt="image-20220610113158764"></p> <p><strong>题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id</strong></p> <p>实现方式1：不成对比较</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> manager_id<span class="token punctuation">,</span> department_id
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span>  manager_id <span class="token operator">IN</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> manager_id
         <span class="token keyword">FROM</span>  employees
         <span class="token keyword">WHERE</span>  employee_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">174</span><span class="token punctuation">,</span><span class="token number">141</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">AND</span>   department_id <span class="token operator">IN</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> department_id
         <span class="token keyword">FROM</span>  employees
         <span class="token keyword">WHERE</span>  employee_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">174</span><span class="token punctuation">,</span><span class="token number">141</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">AND</span> employee_id <span class="token operator">NOT</span> <span class="token operator">IN</span><span class="token punctuation">(</span><span class="token number">174</span><span class="token punctuation">,</span><span class="token number">141</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><p>实现方式2：成对比较</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> manager_id<span class="token punctuation">,</span> department_id
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> <span class="token punctuation">(</span>manager_id<span class="token punctuation">,</span> department_id<span class="token punctuation">)</span> <span class="token operator">IN</span>
          <span class="token punctuation">(</span><span class="token keyword">SELECT</span> manager_id<span class="token punctuation">,</span> department_id
           <span class="token keyword">FROM</span>  employees
           <span class="token keyword">WHERE</span> employee_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">141</span><span class="token punctuation">,</span><span class="token number">174</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">AND</span> employee_id <span class="token operator">NOT</span> <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">141</span><span class="token punctuation">,</span><span class="token number">174</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="_2-3-having-中的子查询"><a href="#_2-3-having-中的子查询" class="header-anchor">#</a> 2.3 HAVING 中的子查询</h3> <ul><li><p>首先执行子查询。</p></li> <li><p>向主查询中的HAVING 子句返回结果。</p></li></ul> <p><strong>题目：查询最低工资大于50号部门最低工资的部门id和其最低工资</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>  department_id<span class="token punctuation">,</span> <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>   employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id
<span class="token keyword">HAVING</span>  <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token operator">&gt;</span>
           <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
            <span class="token keyword">FROM</span>  employees
            <span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> <span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="_2-4-case中的子查询"><a href="#_2-4-case中的子查询" class="header-anchor">#</a> 2.4 CASE中的子查询</h3> <p>在CASE表达式中使用单列子查询：
<strong>题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> last_name<span class="token punctuation">,</span>
   <span class="token punctuation">(</span><span class="token keyword">CASE</span> department_id
   <span class="token keyword">WHEN</span>
      <span class="token punctuation">(</span><span class="token keyword">SELECT</span> department_id <span class="token keyword">FROM</span> departments
  <span class="token keyword">WHERE</span> location_id <span class="token operator">=</span> <span class="token number">1800</span><span class="token punctuation">)</span>     
   <span class="token keyword">THEN</span> <span class="token string">'Canada'</span> <span class="token keyword">ELSE</span> <span class="token string">'USA'</span> <span class="token keyword">END</span><span class="token punctuation">)</span> location
<span class="token keyword">FROM</span>  employees<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="_2-5-子查询中的空值问题"><a href="#_2-5-子查询中的空值问题" class="header-anchor">#</a> 2.5 子查询中的空值问题</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span> job_id
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span> job_id <span class="token operator">=</span>
       <span class="token punctuation">(</span><span class="token keyword">SELECT</span> job_id
        <span class="token keyword">FROM</span>  employees
        <span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Haas'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928985.png" alt="image-20220610113514352"></p> <blockquote><p><strong>子查询不返回任何行</strong></p></blockquote> <h3 id="_2-5-非法使用子查询"><a href="#_2-5-非法使用子查询" class="header-anchor">#</a> 2.5 非法使用子查询</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> last_name
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span> salary <span class="token operator">=</span>
       <span class="token punctuation">(</span><span class="token keyword">SELECT</span>  <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
        <span class="token keyword">FROM</span>   employees
        <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928986.png" alt="image-20220610113546235"></p> <blockquote><p><strong>多行子查询使用单行比较符</strong></p></blockquote> <h2 id="_3-多行子查询"><a href="#_3-多行子查询" class="header-anchor">#</a> 3. 多行子查询</h2> <p>也称为集合比较子查询
内查询返回多行
使用多行比较操作符</p> <h3 id="_3-1-多行比较操作符"><a href="#_3-1-多行比较操作符" class="header-anchor">#</a> 3.1 多行比较操作符</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928987.png" alt="image-20220610113640809"></p> <blockquote><p>体会 ANY 和 ALL 的区别</p></blockquote> <h3 id="_3-2-代码示例"><a href="#_3-2-代码示例" class="header-anchor">#</a> 3.2 代码示例</h3> <p><strong>题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary</strong></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928988.png" alt="image-20220610113713622"></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928989.png" alt="image-20220610113719542"></p> <p><strong>题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary</strong></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928990.png" alt="image-20220610113736474"></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928991.png" alt="image-20220610113741483"></p> <p><strong>题目：查询平均工资最低的部门id</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式1：</span>
<span class="token keyword">SELECT</span> department_id
<span class="token keyword">FROM</span> employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id
<span class="token keyword">HAVING</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>avg_sal<span class="token punctuation">)</span>
    <span class="token keyword">FROM</span> <span class="token punctuation">(</span>
        <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> avg_sal
        <span class="token keyword">FROM</span> employees
        <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id
    <span class="token punctuation">)</span> dept_avg_sal
<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式2：</span>
<span class="token keyword">SELECT</span> department_id
<span class="token keyword">FROM</span> employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id
<span class="token keyword">HAVING</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token keyword">ALL</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> avg_sal
    <span class="token keyword">FROM</span> employees
    <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id
<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h3 id="_3-3-空值问题"><a href="#_3-3-空值问题" class="header-anchor">#</a> 3.3 空值问题</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_name
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> employee_id <span class="token operator">NOT</span> <span class="token operator">IN</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> manager_id
    <span class="token keyword">FROM</span> employees
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928992.png" alt="image-20220610113916702"></p> <h2 id="_4-相关子查询"><a href="#_4-相关子查询" class="header-anchor">#</a> 4. 相关子查询</h2> <h3 id="_4-1-相关子查询执行流程"><a href="#_4-1-相关子查询执行流程" class="header-anchor">#</a> 4.1 相关子查询执行流程</h3> <p>如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 <code>关联子查询</code> 。
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928993.png" alt="image-20220610114002644"></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928994.png" alt="image-20220610114008823"></p> <p>说明：<strong>子查询中使用主查询中的列</strong></p> <h3 id="_4-2-代码示例"><a href="#_4-2-代码示例" class="header-anchor">#</a> 4.2 代码示例</h3> <p><strong>题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id</strong> <strong>方式一：相关子查询</strong></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928995.png" alt="image-20220610114041854"></p> <p><strong>方式二：在 FROM 中使用子查询</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>e1<span class="token punctuation">.</span>department_id
<span class="token keyword">FROM</span> employees e1<span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> department_id<span class="token punctuation">,</span><span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> dept_avg_sal <span class="token keyword">FROM</span> employees <span class="token keyword">GROUP</span>
<span class="token keyword">BY</span> department_id<span class="token punctuation">)</span> e2
<span class="token keyword">WHERE</span> e1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>department_id<span class="token punctuation">`</span></span> <span class="token operator">=</span> e2<span class="token punctuation">.</span>department_id
<span class="token operator">AND</span> e2<span class="token punctuation">.</span>dept_avg_sal <span class="token operator">&lt;</span> e1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><blockquote><p>from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟的表”来使用。</p></blockquote> <p>在ORDER BY 中使用子查询：
<strong>题目：查询员工的id,salary,按照department_name 排序</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span>salary
<span class="token keyword">FROM</span> employees e
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> department_name
    <span class="token keyword">FROM</span> departments d
    <span class="token keyword">WHERE</span> e<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>department_id<span class="token punctuation">`</span></span> <span class="token operator">=</span> d<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>department_id<span class="token punctuation">`</span></span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p><strong>题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>employee_id<span class="token punctuation">,</span> last_name<span class="token punctuation">,</span>e<span class="token punctuation">.</span>job_id
<span class="token keyword">FROM</span>  employees e
<span class="token keyword">WHERE</span> <span class="token number">2</span> <span class="token operator">&lt;=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
            <span class="token keyword">FROM</span>  job_history
            <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> e<span class="token punctuation">.</span>employee_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_4-3-exists-与-not-exists关键字"><a href="#_4-3-exists-与-not-exists关键字" class="header-anchor">#</a> 4.3 EXISTS 与 NOT EXISTS关键字</h3> <ul><li>关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。</li> <li><strong>如果在子查询中不存在满足条件的行：</strong> <ul><li>条件返回 FALSE</li> <li>继续在子查询中查找</li></ul></li> <li><strong>如果在子查询中存在满足条件的行：</strong> <ul><li>不在子查询中继续查找</li> <li>条件返回 TRUE</li></ul></li> <li>NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。</li></ul> <p><strong>题目：查询公司管理者的employee_id，last_name，job_id，department_id信息</strong></p> <p>方式一：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> last_name<span class="token punctuation">,</span> job_id<span class="token punctuation">,</span> department_id
<span class="token keyword">FROM</span>  employees e1
<span class="token keyword">WHERE</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
              <span class="token keyword">FROM</span>  employees e2
              <span class="token keyword">WHERE</span> e2<span class="token punctuation">.</span>manager_id <span class="token operator">=</span>
              e1<span class="token punctuation">.</span>employee_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>方式二：自连接</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> e1<span class="token punctuation">.</span>employee_id<span class="token punctuation">,</span> e1<span class="token punctuation">.</span>last_name<span class="token punctuation">,</span> e1<span class="token punctuation">.</span>job_id<span class="token punctuation">,</span> e1<span class="token punctuation">.</span>department_id
<span class="token keyword">FROM</span>  employees e1 <span class="token keyword">JOIN</span> employees e2
<span class="token keyword">WHERE</span> e1<span class="token punctuation">.</span>employee_id <span class="token operator">=</span> e2<span class="token punctuation">.</span>manager_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>方式三：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span>last_name<span class="token punctuation">,</span>job_id<span class="token punctuation">,</span>department_id
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> employee_id <span class="token operator">IN</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> manager_id
    <span class="token keyword">FROM</span> employees
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><strong>题目：查询departments表中，不存在于employees表中的部门的department_id和department_name</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> department_id<span class="token punctuation">,</span> department_name
<span class="token keyword">FROM</span> departments d
<span class="token keyword">WHERE</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token string">'X'</span>
                  <span class="token keyword">FROM</span>  employees
                  <span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> d<span class="token punctuation">.</span>department_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251928996.png" alt="image-20220610114410758"></p> <h3 id="_4-4-相关更新"><a href="#_4-4-相关更新" class="header-anchor">#</a> 4.4 相关更新</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> table1 alias1
<span class="token keyword">SET</span>  <span class="token keyword">column</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> expression
               <span class="token keyword">FROM</span>  table2 alias2
               <span class="token keyword">WHERE</span> alias1<span class="token punctuation">.</span><span class="token keyword">column</span> <span class="token operator">=</span> alias2<span class="token punctuation">.</span><span class="token keyword">column</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>使用相关子查询依据一个表中的数据更新另一个表的数据。
<strong>题目：在employees中增加一个department_name字段，数据为员工对应的部门名称</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 1）</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employees
<span class="token keyword">ADD</span><span class="token punctuation">(</span>department_name VARCHAR2<span class="token punctuation">(</span><span class="token number">14</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 2）</span>
<span class="token keyword">UPDATE</span> employees e
<span class="token keyword">SET</span> department_name <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> department_name
                       <span class="token keyword">FROM</span>  departments d
                       <span class="token keyword">WHERE</span> e<span class="token punctuation">.</span>department_id <span class="token operator">=</span> d<span class="token punctuation">.</span>department_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h3 id="_4-4-相关删除"><a href="#_4-4-相关删除" class="header-anchor">#</a> 4.4 相关删除</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> table1 alias1
<span class="token keyword">WHERE</span> <span class="token keyword">column</span> operator <span class="token punctuation">(</span><span class="token keyword">SELECT</span> expression
                       <span class="token keyword">FROM</span>  table2 alias2
                       <span class="token keyword">WHERE</span> alias1<span class="token punctuation">.</span><span class="token keyword">column</span> <span class="token operator">=</span> alias2<span class="token punctuation">.</span><span class="token keyword">column</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>使用相关子查询依据一个表中的数据删除另一个表的数据。
<strong>题目：删除表employees中，其与emp_history表皆有的数据</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> employees e
<span class="token keyword">WHERE</span> employee_id <span class="token operator">in</span> 
     <span class="token punctuation">(</span><span class="token keyword">SELECT</span> employee_id
      <span class="token keyword">FROM</span>  emp_history
      <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> e<span class="token punctuation">.</span>employee_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_5-抛一个思考题"><a href="#_5-抛一个思考题" class="header-anchor">#</a> 5. 抛一个思考题</h3> <p><strong>问题</strong>：谁的工资比Abel的高？
<strong>解答</strong>：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式1：自连接</span>
<span class="token keyword">SELECT</span> e2<span class="token punctuation">.</span>last_name<span class="token punctuation">,</span>e2<span class="token punctuation">.</span>salary
<span class="token keyword">FROM</span> employees e1<span class="token punctuation">,</span>employees e2
<span class="token keyword">WHERE</span> e1<span class="token punctuation">.</span>last_name <span class="token operator">=</span> <span class="token string">'Abel'</span>
<span class="token operator">AND</span> e1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span> <span class="token operator">&lt;</span> e2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式2：子查询</span>
<span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span>salary
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> salary <span class="token operator">&gt;</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> salary
    <span class="token keyword">FROM</span> employees
    <span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Abel'</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p><strong>问题</strong>：以上两种方式有好坏之分吗？</p> <p><strong>解答</strong>：自连接方式好！
题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。</p> <p>可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。</p></div></div> <!----> <div class="page-edit"><div class="edit-link"><a href="https://github.com/lcfqzd/vuepress-theme-vdoing/edit/master/docs/04.数据库/01.MySQL笔记/09.子查询.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="tags"><a href="/noteslibrary/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/noteslibrary/pages/2dfe6d/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">聚合函数</div></a> <a href="/noteslibrary/pages/70a90b/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">创建和管理表</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/noteslibrary/pages/2dfe6d/" class="prev">聚合函数</a></span> <span class="next"><a href="/noteslibrary/pages/70a90b/">创建和管理表</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2018-2023
    <span>LCFQZD | MIT License</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"></div></div>
    <script src="/noteslibrary/assets/js/app.dc0c3d24.js" defer></script><script src="/noteslibrary/assets/js/2.a664539c.js" defer></script><script src="/noteslibrary/assets/js/96.440e6bbe.js" defer></script>
  </body>
</html>
